package cn.com.dhcc.ddi.stat.dao;

import java.util.List;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;
import cn.com.dhcc.app.core.dao.BaseDao;
import cn.com.dhcc.app.core.exception.DaoException;
import cn.com.dhcc.app.core.handler.modulehandler.WhereCondition;
import cn.com.dhcc.app.core.handler.pagerhandler.Pager;
import cn.com.dhcc.app.core.handler.pagerhandler.PagerData;
import cn.com.dhcc.app.core.service.Log;
import cn.com.dhcc.app.pub.CM;
import cn.com.dhcc.app.pub.core.util.dateformatter.DateFormatter;
import cn.com.dhcc.ddi.node.vo.DataCategory;
import cn.com.dhcc.ddi.node.vo.NodeInfo;
import cn.com.dhcc.ddi.node.vo.wapper.DataCategoryRowWapper;
import cn.com.dhcc.ddi.node.vo.wapper.NodeInfoRowWapper;
import cn.com.dhcc.ddi.stat.vo.DataSyncStatVo;
import cn.com.dhcc.ddi.stat.vo.wrapper.DataSyncStatRowWrapper;

/**
 * 
    * @ClassName: DataSynDao
    * @author 18315
    * @date 2016-12-26
    *
 */
@Repository
public class DataSynDao extends BaseDao {
	
	private static Logger logger = Log.getLogger(Log.LOGTYPE.DDI);
	/**
	 * 
	    * @Title: getDailyNodeSendStatistics
	    * @Description: 按节点每日统计
	    * @param @return
	    * @param @throws DaoException    参数
	    * @return List<DataSyncStatVo>    返回类型
	    * @throws
	 */
	public List<DataSyncStatVo> getDailyNodeSendStatistics() throws DaoException {
		String sql = " SELECT t6.node_id AS node_id, 0 AS exch_type, t4.cata_ID, sum(t4.total) AS total, SUM(t4.data_size) AS data_size FROM "
				   + "   (SELECT t1.id as task_id, "
				   + "           t1.task_name, "
				   + "           t1.mp_id, "
				   + "           t1.DATE_CATEGORY_ID as cata_id, "
				   + "           IFNULL(SUM(t2.total), '0') AS data_size, "
				   + "           COUNT(t2.task_id) AS total "
				   + "     FROM "
				   +         SI_TABLE("t_ddi_task") + " t1 "
				   + "     LEFT JOIN "
				   + "       (SELECT t3.task_id, t3. total FROM "
				   +             SI_TABLE("t_ddi_task_log") + " t3 "
				   + "        WHERE t3.group_id IS NULL AND DATE(t3.LINKENDTIME) = SUBDATE(CURRENT_DATE(), INTERVAL 1 DAY) "
				   + "              AND t3.LOG_STATUS = '1') t2 "
				   + "     ON t1.id = t2.task_id "
				   + "     GROUP BY task_id) t4 "
				   + " LEFT JOIN "
				   +       SI_TABLE("t_ddi_ds_mapping") + " t5 "
				   + " ON t4.mp_id = t5.id "
				   + " LEFT JOIN "
				   +       SI_TABLE("t_ddi_datasource") + " t6 "
				   + " ON t6.id = t5.SRC_DS_ID"
				   + " GROUP BY node_id, cata_id";
						   
			    
		
			return CM.getDao().list("按节点获取统计信息", sql, new DataSyncStatRowWrapper(),new Object[] {});
	}
	
	public List<DataSyncStatVo> getDailyNodeRecvStatistics() throws DaoException {
		String sql = " SELECT t6.node_id AS node_id, 1 AS exch_type, t4.cata_ID, sum(t4.total) AS total, SUM(t4.data_size) AS data_size FROM "
				   + "   (SELECT t1.id as task_id, "
				   + "           t1.task_name, "
				   + "           t1.mp_id, "
				   + "           t1.DATE_CATEGORY_ID as cata_id, "
				   + "           IFNULL(SUM(t2.total), '0') AS data_size, "
				   + "           COUNT(t2.task_id) AS total "
				   + "     FROM "
				   +         SI_TABLE("t_ddi_task") + " t1 "
				   + "     LEFT JOIN "
				   + "       (SELECT t3.task_id, t3. total FROM "
				   +             SI_TABLE("t_ddi_task_log") + " t3 "
				   + "        WHERE t3.group_id IS NULL AND DATE(t3.LINKENDTIME) = SUBDATE(CURRENT_DATE(), INTERVAL 1 DAY) "
				   + "              AND t3.LOG_STATUS = '1') t2 "
				   + "     ON t1.id = t2.task_id "
				   + "     GROUP BY task_id) t4"
				   + " LEFT JOIN "
				   +       SI_TABLE("t_ddi_ds_mapping") + " t5 "
				   + " ON t4.mp_id = t5.id "
				   + " LEFT JOIN "
				   +       SI_TABLE("t_ddi_datasource") + " t6 "
				   + " ON t6.id = t5.DEST_DS_ID"
				   + " GROUP BY node_id, cata_id";
						   
			    
		
			return CM.getDao().list("按节点获取统计信息", sql, new DataSyncStatRowWrapper(),new Object[] {});
	}
	
	/**
	 * 
	 * @param start
	 * @param end
	 * @param condition
	 * @param cata_id
	 * @param type
	 * @return
	 * @throws DaoException
	 */
	public List<DataSyncStatVo> getDataStatData(String start, String end, String condition, String cataId, String type) throws DaoException {
		/*Object[] param = new Object[] {};
		String cataWhere = "";
		if(!cataId.equals("all")) {
			cataWhere = "AND t1.cata_id = ? ";
			param = new Object[] {type, start, end, cataId};
		} else {
			param = new Object[] {type, start, end};
		}
		
		String sql = "SELECT t1.node_id, t1.cata_id, t1.exch_type, SUM(t1.total) AS total, SUM(t1.`data_size`) AS data_size, t1.`stat_date` FROM "
				   + SI_TABLE("t_ddi_data_sync_statistics") +" t1 "
				   + " WHERE t1.exch_type = ? AND t1.stat_date BETWEEN ? AND ? " + cataWhere
				   + " GROUP BY t1.node_id, t1.cata_id";*/
		
		Object[] param = new Object[] {};
		String cataWhere = "";
		if(cataId != null) {
			cataWhere = " and t6.cata_id = ? ";
			param = new Object[] {start, end, start, end, type, start, end, cataId};
		} else {
			param = new Object[] {start, end, start, end, type, start, end};
		}
		
		
		String sql = " SELECT t6.cata_id, t8.name AS datacatagoryName, GROUP_CONCAT(t6.total ORDER BY t6.orgcode desc) AS total_list, "
				   + "             GROUP_CONCAT(t6.data_size ORDER BY t6.orgcode desc) AS data_size_list "
				   + "      FROM "
				   + "         (SELECT t3.orgcode,t3.cata_id, IFNULL(t4.total, 0) AS total, "
				   + "                 IFNULL(t4.data_size, 0) AS data_size "
				   + "          FROM "
				   + "              (SELECT * FROM "
				   + "					(SELECT DISTINCT node_id, node.orgcode FROM " + SI_TABLE("t_ddi_data_sync_statistics")+" stat"
				   +"						LEFT JOIN " + SI_TABLE("t_ddi_node_info node")+" ON node.ID = stat.node_id"
				   +"       	 			WHERE node.ID IS NOT NULL "
				+ "                   and stat_date BETWEEN ? AND ? "+ condition +" ORDER BY node.orgcode) t1 "
				   + "                   INNER JOIN "
				   + "                  (SELECT DISTINCT cata_id FROM " + SI_TABLE("t_ddi_data_sync_statistics")+" stat"
				   +"						LEFT JOIN " + SI_TABLE("t_ddi_node_info node")+" ON node.ID = stat.node_id"
				   +"       	 			WHERE node.ID IS NOT NULL "
				   + "                   and stat_date BETWEEN ? AND ? "+ condition +" ) t2 "
				   + "               ) t3 "
				   + "          LEFT JOIN "
				   + "              (SELECT t5.node_id, t5.cata_id, SUM(t5.total) as total, SUM(t5.data_size) as data_size FROM " + SI_TABLE("t_ddi_data_sync_statistics") + " t5 "
				   + " 					LEFT JOIN " + SI_TABLE("t_ddi_node_info")+" node"
				   + " 							ON node.id = t5.node_id"
				   + " 					WHERE node.id is not null"
				   + "               and t5.exch_type = ? AND t5.stat_date BETWEEN ? AND ? GROUP BY node_id, cata_id) t4 "
				   + "          ON t3.cata_id = t4.cata_id AND t3.node_id = t4.node_id) t6 "
					+ " LEFT JOIN "
					+       SI_TABLE("t_ddi_data_catagory") + " t8 "
					+ " ON t6.cata_id = t8.id "
				   + " where 1=1 "+cataWhere+" GROUP BY t6.cata_id ";
		return CM.getDao().list("按节点获取统计信息", sql, new DataSyncStatRowWrapper(),param);
	}
	
	
	public List<DataSyncStatVo> getDistinctNodes(WhereCondition nodeParameter,String start, String end) throws DaoException {
		String sql = " SELECT t1.name as nodeName FROM "
				   	+      SI_TABLE("t_ddi_node_info") +" t1 "
				   	+"  where t1.id in (SELECT DISTINCT node_id FROM "
					+         SI_TABLE("t_ddi_data_sync_statistics")
					+ "    WHERE stat_date BETWEEN ? AND ?)  ";

		return CM.getDao().list("获取统计中的所有节点信息", sql, new DataSyncStatRowWrapper(),nodeParameter,new Object[] {start, end});
	}
	
	public List<DataSyncStatVo> getDistinctCates(WhereCondition parameter,String start, String end) throws DaoException {
		String sql = " SELECT DISTINCT t2.name as datacatagoryName FROM "
				   +   SI_TABLE("t_ddi_data_sync_statistics") +" t1 "
				   + " LEFT JOIN "
				   +      SI_TABLE("t_ddi_data_catagory") +" t2 "
				   + " ON t1.cata_id = t2.id "
				   +" WHERE t1.stat_date BETWEEN ? and ?";

		return CM.getDao().list("获取统计中的所有数据分类信息", sql, new DataSyncStatRowWrapper(),parameter,new Object[] {start, end});
	}
	
	public PagerData<DataSyncStatVo> getDataStatPagerData(Pager pager, String start, String end, String cataId, WhereCondition condition) throws DaoException {
		String cataWhere = "";
		if(cataId != null && !cataId.isEmpty()) {
			cataWhere = " AND cata_id = '" + cataId + "' ";
		}
		
		String sql =
				"SELECT t2.id node_id,t2.name nodeName,t1.exch_type,t3.id cata_id,t3.name datacatagoryName,t1.total,t1.data_size ,t1.stat_date FROM " +
						" " + SI_TABLE("t_ddi_data_sync_statistics") + " t1" + 
						"   LEFT JOIN " + 
						" " + SI_TABLE("t_ddi_node_info") +" t2" + 
						"   ON t1.node_id = t2.id " + 
						"   LEFT JOIN " + 
						" " + SI_TABLE("t_ddi_data_catagory") + " t3" + 
						"   ON t1.cata_id = t3.id " + 
						"   WHERE t1.stat_date BETWEEN ? AND ? "+cataWhere+" " ;

		
		return CM.getDao().getPagerList("获取信息列表", sql,
				new DataSyncStatRowWrapper(), pager, condition, new Object[] {start, end});
	}
	
	/**
	 * 列表
	    * @Title: getTaskSyncStatInfo
	    * @Description: TODO(这里用一句话描述这个方法的作用)
	    * @param @return
	    * @param @throws DaoException    参数
	    * @return List<TaskSyncStatVo>    返回类型
	    * @throws
	 */
	public PagerData<DataSyncStatVo> list(Pager pager, WhereCondition condition) throws DaoException {
	String msg = "";
	String sql = "select t1.*, t2.name nodeName, t3.name datacatagoryName FROM "+ SI_TABLE("t_ddi_data_sync_statistics") + " t1"						   
		    + " JOIN " + SI_TABLE("t_ddi_node_info") + " t2 "
		    + " ON t1.node_id = t2.id"
		    + " JOIN " + SI_TABLE("t_ddi_data_catagory") +" t3 "
		    + " ON t1.cata_id=t3.id"
		    + " where 1=1 ";
	try {
		return CM.getDao().getPagerList("获取信息列表", sql,
				new DataSyncStatRowWrapper(), pager, condition, new Object[] {});
	} catch (DaoException e) {
		msg = "失败";
		logger.error(msg, e);
		throw new DaoException(msg, e);
	}
}
	/**
	 * 获取所有节点信息
	 * @return
	 * @throws DaoException
	 */
	public List<NodeInfo> getNodes() throws DaoException {
		String msg = "";
		String sql = "select id, name from "+SI_TABLE("t_ddi_node_info") + " where status='1' order by  orgcode"; 
		
		try {
			return CM.getDao().list("获取节点信息", sql,
					new NodeInfoRowWapper(),new Object[] {});
		} catch (DaoException e) {
			msg = "获取节点信息失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	
	/**
	 * 获取数据分类列表
	 * @param DataCategory　
	 * @return
	 * @throws DaoException
	 */
	public List<DataCategory> getDataCategory() throws DaoException {
		String msg = "";
		String sql="select DISTINCT t1.id,t1.name"
				   + " from "+ SI_TABLE("t_ddi_data_catagory")+" t1, "
				   + SI_TABLE("t_ddi_data_sync_statistics")+" t2 "
				   + " where t1.id = t2.cata_id"
				   + " and t1.status= 1"; 
				  
		try {
			return CM.getDao().list("获取数据分类列表", sql,
					new DataCategoryRowWapper(), new Object[] {});
		} catch (DaoException e) {
			msg = "获取列表失败";
			logger.error("获取列表失败", e);
			throw new DaoException(msg, e);
		}
	}
	
	/**
	 * 获取数据分类列表(获取与用户相关的数据分类)
	 * @param DataCategory　
	 * @return
	 * @throws DaoException
	 */
	public List<DataCategory> getUserDataCategory(WhereCondition condition) throws DaoException {
		String msg = "";
		String sql="select DISTINCT t1.id,t1.name"
				   + " from "+ SI_TABLE("t_ddi_data_catagory")+" t1, "
				   + SI_TABLE("t_ddi_data_sync_statistics")+" t2 "
				   + " where t1.id = t2.cata_id"
				   + " and t1.status= 1"; 
				  
		try {
			return CM.getDao().list("获取数据分类列表", sql,
					new DataCategoryRowWapper(), condition, new Object[] {});
		} catch (DaoException e) {
			msg = "获取列表失败";
			logger.error("获取列表失败", e);
			throw new DaoException(msg, e);
		}
	}
	
	/**
	 * 获取24小时接收数据量/频次统计
	 * @param id 节点id
	 * @return
	 * @throws DaoException
	 */
	public List<NodeInfo> getReceiveTotalByHour(String id) throws DaoException {
		String msg = "";
		String sql = "SELECT HOUR(t2.MTIME) AS hour, t1.id,SUM(t2.TOTAL) AS COUNT FROM"
					 + SI_TABLE("t_ddi_node_info")+ "t1" 
					 + " JOIN" +SI_TABLE("t_ddi_datasource")+ " t3 " 
					 + " ON (t1.id = t3.node_id)" 
					 + " JOIN" +SI_TABLE("T_DDI_DS_MAPPING")+ " t4 " 
					 + " ON t3.id = t4.src_ds_id" 
					 + " JOIN" +SI_TABLE("t_ddi_task")+ " t5 " 
					 + " ON (t4.id = t5.mp_id)" 
					 + " JOIN"+ SI_TABLE("t_ddi_task_log ") +" t2 "
					 + " ON (t2.task_id = t5.id)" 
					 + " WHERE T1.id= ? AND t2.MTIME LIKE ? AND t2.LOG_STATUS= 1 AND t2.LINKTYPE= 3 "
					 + " GROUP BY DATE_FORMAT(t2.MTIME, '%Y-%m-%d %H ')";
		
		String date = DateFormatter.getToday("yyyy-MM-dd") + "%";
		
		try {
			return CM.getDao().list("获取24小时接受统计", sql, new NodeInfoRowWapper(), new Object[] {id, date});
		} catch (DaoException e) {
			msg = "数据源失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	public List<NodeInfo> getReceiveDatasByHour(String id) throws DaoException {
		String msg = "";
		String sql = "SELECT HOUR(t2.MTIME) AS hour, t1.id,SUM(t2.id) AS COUNT FROM"
					 + SI_TABLE("t_ddi_node_info")+ "t1" 
					 + " JOIN" +SI_TABLE("t_ddi_datasource")+ " t3 " 
					 + " ON (t1.id = t3.node_id)" 
					 + " JOIN" +SI_TABLE("T_DDI_DS_MAPPING")+ " t4 " 
					 + " ON t3.id = t4.src_ds_id" 
					 + " JOIN" +SI_TABLE("t_ddi_task")+ " t5 " 
					 + " ON (t4.id = t5.mp_id)" 
					 + " JOIN"+ SI_TABLE("t_ddi_task_log ") +" t2 "
					 + " ON (t2.task_id = t5.id)" 
					 + " WHERE T1.id= ? AND T2.MTIME LIKE ? AND t2.LOG_STATUS= 1 AND t2.LINKTYPE= 3 "
					 + " GROUP BY DATE_FORMAT(t2.MTIME, '%Y-%m-%d %H ')";
		
		String date = DateFormatter.getToday("yyyy-MM-dd") + "%";
		
		try {
			return CM.getDao().list("获取24小时接受统计", sql, new NodeInfoRowWapper(), new Object[] {id, date});
		} catch (DaoException e) {
			msg = "数据源失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	/**
	 * 获取24小时发送收数据量/频次统计
	 * @param id 节点id
	 * @return
	 * @throws DaoException
	 */
	public List<NodeInfo> getSendTotalByHour(String id) throws DaoException {
		String msg = "";
		String sql = "SELECT HOUR(CREATED) AS hour, t1.id,SUM(t2.TOTAL) AS COUNT FROM"
				 + SI_TABLE("t_ddi_node_info")+ " t1 " 
				 + " JOIN" +SI_TABLE("t_ddi_datasource")+ " t3 " 
				 + " ON (t1.id = t3.node_id)" 
				 + " JOIN" +SI_TABLE("T_DDI_DS_MAPPING")+ " t4 " 
				 + " ON t3.id = t4.src_ds_id" 
				 + " JOIN" +SI_TABLE("t_ddi_task")+ " t5 " 
				 + " ON (t4.id = t5.mp_id)" 
				 + " JOIN"+ SI_TABLE("t_ddi_task_log ")+" t2 " 
				 + " ON (t2.task_id = t5.id)" 
				 + " WHERE t1.id= ? AND t2.MTIME LIKE ? AND t2.LOG_STATUS= 1 AND t2.LINKTYPE= 1"
				 + " GROUP BY DATE_FORMAT(MTIME, '%Y-%m-%d %H ')";
		
		String date = DateFormatter.getToday("yyyy-MM-dd") + "%";
		
		try {
			return CM.getDao().list("获取24小时发送统计", sql, new NodeInfoRowWapper(), new Object[] {id, date});
		} catch (DaoException e) {
			msg = "数据源失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	public List<NodeInfo> getSendDatasByHour(String id) throws DaoException {
		String msg = "";
		String sql = "SELECT HOUR(CREATED) AS hour, t1.id,SUM(t2.id) AS COUNT FROM"
				 + SI_TABLE("t_ddi_node_info")+ "t1" 
				 + " JOIN" +SI_TABLE("t_ddi_datasource")+ "t3" 
				 + " ON (t1.id = t3.node_id)" 
				 + " JOIN" +SI_TABLE("T_DDI_DS_MAPPING")+ "t4" 
				 + " ON t3.id = t4.src_ds_id" 
				 + " JOIN" +SI_TABLE("t_ddi_task")+ "t5" 
				 + " ON (t4.id = t5.mp_id)" 
				 + " JOIN"+ SI_TABLE("t_ddi_task_log ")+" t2 "
				 + " ON (t2.task_id = t5.id)" 
				 + " WHERE T1.id= ? AND T2.MTIME LIKE ? AND t2.LOG_STATUS= 1 AND t2.LINKTYPE= 1"
				 + " GROUP BY DATE_FORMAT(MTIME, '%Y-%m-%d %H ')";
		
		String date = DateFormatter.getToday("yyyy-MM-dd") + "%";
		
		try {
			return CM.getDao().list("获取24小时发送统计", sql, new NodeInfoRowWapper(), new Object[] {id, date});
		} catch (DaoException e) {
			msg = "数据源失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	
	/**
     * 新增
     * @param obj
     * @return 成功次数
     * @throws DaoException
     */
    public int insert(DataSyncStatVo obj) throws DaoException {
        return getDao().insertModule("新增",obj);
    }

}
